package com.example.cores_sqlite.utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.Nullable;

import com.example.cores_sqlite.entity.User;

import java.util.ArrayList;
import java.util.List;

public class UserDBHelper extends SQLiteOpenHelper {
    static private final String DB_NAME = "user.db";
    static private final String TABLE_NAME = "user_info";
    static private final int DB_VERSION = 2;
    private static UserDBHelper instance = null;
    private SQLiteDatabase reader = null;
    private SQLiteDatabase writer = null;


    // 私有构造器，单例模式
    private UserDBHelper(@Nullable Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    // 获取实例
    public static UserDBHelper getInstance(@Nullable Context context) {
        Log.d("zhoutao", "UserDBHelper.getInstance");
        if (instance == null) {
            instance = new UserDBHelper(context);
        }
        return instance;
    }

    // 打开读连接
    public SQLiteDatabase openReadLink() {
        if (reader == null || !reader.isOpen()) {
            reader = instance.getReadableDatabase();
        }
        return reader;
    }

    // 打开写连接
    public SQLiteDatabase openWriteLink() {
        if (writer == null || !writer.isOpen()) {
            writer = instance.getWritableDatabase();
        }
        return writer;
    }

    // 关闭连接
    public void close() {
        if (reader != null && reader.isOpen()) {
            reader.close();
            reader = null;
        }

        if (writer != null && writer.isOpen()) {
            writer.close();
            writer = null;
        }
    }


    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        Log.d("zhoutao", "onCreate: create database table");
        // 创建数据库，执行建表语句
        String sql = "CREATE table if not exists " + TABLE_NAME + " (" +
                "_id integer primary key autoincrement not null," +
                "name varchar," +
                "age integer," +
                "height long," +
                "weight float," +
                "married integer" +
                ");";
        sqLiteDatabase.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        String addPhoneFields = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN phone varchar";
        sqLiteDatabase.execSQL(addPhoneFields);

        String addPasswordFields = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN password varchar";
        sqLiteDatabase.execSQL(addPasswordFields);
    }

    public long insert(User user) {
        ContentValues values = new ContentValues();
        values.put("name", user.name);
        values.put("age", user.age);
        values.put("height", user.height);
        values.put("weight", user.weight);
        values.put("married", user.married);
        return writer.insert(TABLE_NAME, "name", values);
    }

    public long delete(String name) {
        return writer.delete(
                TABLE_NAME,
                "name=?",
                new String[]{name}
        );
    }

    public long update(User user) {
        ContentValues values = new ContentValues();
        values.put("name", user.name);
        values.put("age", user.age);
        values.put("height", user.height);
        values.put("weight", user.weight);
        values.put("married", user.married);

        return writer.update(
                TABLE_NAME,
                values,
                "name=?",
                new String[]{user.name}
        );
    }

    public List<User> select() {
        List<User> usersList = new ArrayList<>();

        Cursor cursor = reader.query(TABLE_NAME, null,
                null, null, null, null, null);
        while (cursor.moveToNext()) {
            User user = new User();
            user.id = cursor.getInt(0);
            user.name = cursor.getString(1);
            user.age = cursor.getInt(2);
            user.height = cursor.getLong(3);
            user.weight = cursor.getFloat(4);
            user.married = cursor.getInt(5) == 1 ? true : false;

            usersList.add(user);
        }

        return usersList;
    }

    public List<User> selectByName(String name) {
        List<User> usersList = new ArrayList<>();

        Cursor cursor = reader.query(TABLE_NAME, null,
                "name=?", new String[]{name}, null, null, null);
        while (cursor.moveToNext()) {
            User user = new User();
            user.id = cursor.getInt(0);
            user.name = cursor.getString(1);
            user.age = cursor.getInt(2);
            user.height = cursor.getLong(3);
            user.weight = cursor.getFloat(4);
            user.married = cursor.getInt(5) == 1 ? true : false;

            usersList.add(user);
        }

        return usersList;
    }
}
